'''
操作数据库
'''

import pymysql

def connect(db_info):
    '''
    连接数据库
    :param db_info:  存放数据库信息字典
    :return:  连接对象
    '''
# db = {"host":"192.168.150.96","post":3306,"user":"root","pwd":"123456","dbname":"apple"}
    try:
        conn = pymysql.connect(
            user=db_info["user"],
            password=db_info["pwd"],
            host=db_info["host"],
            database=db_info["dbname"],
            port=db_info["port"],
            charset="utf8"
        )
        print("连接数据库成功")
        return conn
    except Exception as e:
        print(f"连接数据库失败，异常为: {e}")



def disconnect(conn):

    '''

    :param conn:
    :return:
    '''

    try:
        conn.close()
        print("断开数据库成功")
    except Exception as e:
        print(f"断开数据库异常，异常为: {e}")



def execute(conn,sql):
    '''
    执行sql语句
    :param conn:  connect方法返回的对象
    :param sql:  要执行的sql语句
    :return:
    '''

    try:
        c = conn.cursor()  # 获取游标
        c.execute(sql)
        conn.commit()
        r = c.fetchall() # 获取结果
        print(f"执行sql语句成功：{sql}")
        return r


    except Exception as e:
        print(f"执行sql语句失败：{sql}，异常为: {e}")

def delete_user(db,phone):
    '''

    :param db:
    :param phone:
    :return:
    '''
    c = connect(db)
    execute(c, f"DELETE FROM member WHERE mobilephone='{phone}'")
    disconnect(c)


# 查询id

def get_id(db,phone):
    c = connect(db)
    r = execute(c, f"SELECT Id FROM member WHERE mobilephone='{phone}'")
    disconnect(c)
    try:
        return r[0][0]
    except Exception as e:
        print(f"未查询到，异常为:{e}")


def delete_loan(db,id):
    '''

    :param db:
    :param phone:
    :return:
    '''
    # DELETE FROM loan WHERE memberid=67195
    c = connect(db)
    execute(c, f"DELETE FROM loan WHERE memberid='{id}'")
    disconnect(c)




if __name__ == '__main__':
    db = {"host":"192.168.150.96","port":3306,"user":"root","pwd":"123456","dbname":"apple"}

    id = get_id(db,"17691230367")
    print(id)
